﻿----- tim kiem khach hang theo cac tiêu chí ------

CREATE
--ALTER 
PROC proc_TimKhachHang
@seach varchar(50)
AS
BEGIN
	SELECT * FROM KHACHHANG WHERE MaKH like +'%'+ @seach +'%' OR TenKH like +'%'+ @seach +'%' OR DThoai like +'%'+ @seach +'%' OR Email like +'%'+ @seach +'%' OR Cty like +'%'+ @seach +'%' OR DChi like +'%'+ @seach +'%'
END

GO

------- Thêm một khách hàng ----------------------------
CREATE 
--ALTER 
PROC proc_ThemKhachHang
@makh varchar(6),@tenkh nvarchar(50),@dthoai int, @dchi nvarchar(50),@cty nvarchar(50), @email nvarchar(50),@res int output
AS
BEGIN TRAN
	BEGIN TRY
		IF(@makh not in (Select MaKH From KHACHHANG))
		BEGIN
			Insert into KHACHHANG(MaKH,TenKH,DThoai,DChi,Cty,Email) values (@makh,@tenkh,@dthoai,@dchi,@cty,@email)
			set @res = 1
		END
		ELSE 
		BEGIN
			PRINT 'lap hoa don that bai'
			set @res = 0	
		END
	END TRY
	BEGIN CATCH
		--RAISERROR (N'LỖI HỆ THỐNG',16,1)
		--RAISERROR 
		
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		ROLLBACK TRAN
		RETURN
	END CATCH
COMMIT TRAN
GO

Declare @res int
EXEC proc_ThemKhachHang 'KH005','Nguyen Van A',090,'Q.7','cty abc','l@yahoo.com',@res output
print @res

-----Sửa thông tin một khách hàng ------------------
CREATE 
--ALTER 
PROC proc_CapNhatKhachHang
@makh varchar(6),@tenkh nvarchar(50),@dthoai int, @dchi nvarchar(50),@cty nvarchar(50), @email nvarchar(50),@res int output
AS
BEGIN TRAN
	BEGIN TRY
		IF(@makh in (Select MaKH From KHACHHANG))
		BEGIN
			UPDATE KHACHHANG set TenKH =@tenkh,DThoai = @dthoai ,DChi=@dchi ,Cty=@cty ,Email =@email WHERE MaKH=@makh
			set @res = 1
		END
		ELSE 
		BEGIN
			PRINT 'lap hoa don that bai'
			set @res = 0	
		END
	END TRY
	BEGIN CATCH
		--RAISERROR (N'LỖI HỆ THỐNG',16,1)
		--RAISERROR 
		
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		ROLLBACK TRAN
		RETURN
	END CATCH
COMMIT TRAN
GO

Declare @res int
EXEC proc_CapNhatKhachHang 'KH005','Nguyen Van A',0907,'Q.7','cty abc','l@yahoo.com',@res output
print @res


EXEC proc_TimKhachHang '1'

select * from KHACHHANG
select * from DONHANG
